USE [NANS21V]
GO

/****** オブジェクト: SqlProcedure [dbo].[TeamList] スクリプト日付: 2014/05/07 10:54:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TeamList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[TeamList];
GO

CREATE PROCEDURE [dbo].[TeamList]
	@TAIKAINO int,
	@KYOGINO int,
	@flg int
AS
	SELECT 
	KS.KYOGISYANO,
	dbo.IsRelay(KS.KYOGISYANO,SM.SYUMOKUKUBUN) AS KYOGISYANO1, 
	(KS.KYOGISYAKANA + '<br>' + KS.KYOGISYAMEI)AS KYOGISYAMEI,
	S.SYOZOKUNO, 
	( S.SYOZOKUKANA + '<br>' + S.SYOZOKUMEI) AS SYOZOKUMEI,
	ST.SYOZOKUTIMEI, 
	dbo.Kiroku(SK.KIROKU,SM.SYUMOKUKUBUN,SM.KIROKUKUBUN,SK.KIROKUFLG) AS KIROKU,
	ISNULL(WT.COUNT,0) AS COUNT,
	SM.KIROKUKUBUN, 
	SM.SYUMOKUKUBUN, 
	SK.KIROKUFLG 
FROM 
	TBL_KYOGISYA AS KS 
LEFT OUTER JOIN 
	(SELECT TEAMNO,COUNT(KYOGISYANO) AS COUNT 
		FROM 
			TBL_TEAM 
		WHERE 
			TAIKAINO = @TAIKAINO 
			AND TEAMNO 
		IN (SELECT KYOGISYANO FROM TBL_KYOGISYA WHERE TAIKAINO = @TAIKAINO AND KYOGISYAFLG = 1) 
	GROUP BY TEAMNO ) AS WT 
ON 
	KS.KYOGISYANO = WT.TEAMNO, 
	TBL_SYOZOKU AS S,
	TBL_SANKAKYOGI AS SK, 
	TBL_KYOGI AS K , 
	TBL_SYOZOKUTI AS ST, 
	TBL_SYUMOKU_MASTER AS SM 
WHERE 
	KS.SYOZOKUNO1 = S.SYOZOKUNO 
	AND KS.TAIKAINO = S.TAIKAINO 
	AND KS.TAIKAINO = SK.TAIKAINO 
	AND KS.KYOGISYANO = SK.KYOGISYANO 
	AND SK.TAIKAINO = K.TAIKAINO 
	AND SK.KYOGINO = K.KYOGINO 
	AND K.SYUMOKUNO = SM.SYUMOKUNO 
	AND S.SYOZOKUTINO = ST.SYOZOKUTINO 
	AND KS.TAIKAINO = @TAIKAINO 
	AND KS.KYOGISYAFLG = 1 
	AND SK.KYOGINO = @KYOGINO 
ORDER BY 	
CASE WHEN KIROKU = '0' and @flg = 2 THEN 1 ELSE 0 END,
CASE WHEN @flg = 1 THEN KS.KYOGISYANO WHEN @flg = 2 THEN SK.KIROKU END